package com.is.control;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.is.dbconn.DBConnect;
import com.is.model.User;

public class UserControl {
	public static User loginCheck(String name){
		DBConnect dbc = new DBConnect();
		String sql = "select * from user where username ='" + name + "'";
		dbc.prepareStatement(sql);
		ResultSet rs = null;
		User u = new User();
		try{
			rs = dbc.executeQuery();
			if(rs.next()){
				getRs(u, rs);
				return u;
			} else{
				return null;
			}
			
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("调用loginCheck函数失败");
			return null;
		} finally{
			dbc.close();
		}
	}
	
	public static boolean insertUser(User u){
		DBConnect dbConnect = new DBConnect();
		String sql = "insert into user (name,username,password,role,picUrl,position,political,birthday,eduLevel,email,phone,sex,professionalSkill) values(?,?,?,?,?,?,?,?,?,?,?,?,?)";
		dbConnect.prepareStatement(sql);
		try {
			setValue(u, dbConnect);
			if(dbConnect.executeUpdate()){
				return true;
			}
			
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			System.out.println("insertUser Error");
		}finally{
			dbConnect.close();
		}
		return  false;
		
	}
	
	
	public static boolean deleteUserById(int id){
		DBConnect dbConnect =new DBConnect();
		String sql = "delete from user where id=" + id;
		System.out.println(sql+"ddddd");
		dbConnect.prepareStatement(sql);
		try {
			if(dbConnect.executeUpdate()){
				return true;
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			System.out.println("deleteUser ERROR");
		}finally{
			dbConnect.close();
		}
		return false;
	}
	
	
	public static User getUserById(int uid){
		DBConnect dbc = new DBConnect();
		String sql = "select * from user where id = '"+uid+"'";
		ResultSet rs = null;
		User u = null;
		dbc.prepareStatement(sql);
		try {
			rs = dbc.executeQuery();
			if(rs.next()){
				u = new User();
				getRs(u, rs);
			}
			return u;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			System.out.println("getUserById EEOR");
			return u;
		}finally{
			dbc.close();
		}
	}
	public static boolean updateUser(User u, int id){
		DBConnect dbc = new DBConnect();
		String sql = "update user set name=? ,username=? ,password=? ,role=? ,picUrl=? ,position=?"+
		" ,political=? ,birthday=? ,eduLevel=? ,email=? ,phone=? ,sex=? ,professionalSkill=? where id ="+ id;
		dbc.prepareStatement(sql);
		try{
			setValue(u, dbc);
			dbc.executeUpdate();
			return true;
		} catch(Exception e){
			e.printStackTrace();
			System.out.println("ddddddddddddd");
		} finally{
			dbc.close();
		}
		return false;
		
	}
	/**获取所有的老师
	 * role为“”时，显示所有的
	 * @param role
	 * @return
	 */
	public static List<User> getAllUsersByRole(String role){
		DBConnect dbConnect = new DBConnect();
		List<User> users = new ArrayList<User>();
		String sql = (role==null||role.equals(""))?"select * from user order by username":"select * from user where role = '"+role+"' order by username";
		dbConnect.prepareStatement(sql);
		ResultSet rSet = null;
		try {
			rSet = dbConnect.executeQuery();
			while(rSet.next()){
				User u = new User();
				getRs(u, rSet);
				users.add(u);
			}
			return users;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			System.out.println("getAllUsersByRole ERROOR");
			return null;
		}finally{
			dbConnect.close();
		}
	}
	
	
	public static void setValue(User u,DBConnect dbc){
		//调用此方法需注意字段顺序(与sql语句保持一致)
		int index=1;
		try{
		dbc.setString(index++, u.getName());
		dbc.setString(index++, u.getUsername());
		dbc.setString(index++, u.getPassword());
		dbc.setString(index++, u.getRole());
		dbc.setString(index++, u.getPicUrl());
		dbc.setString(index++, u.getPosition());
		dbc.setString(index++, u.getPolitical());
		dbc.setString(index++, u.getBirthday());
		dbc.setString(index++, u.getEduLevel());
		dbc.setString(index++, u.getEmail());
		dbc.setString(index++, u.getPhone());
		dbc.setString(index++, u.getSex());
		dbc.setString(index++, u.getProfessionalSkill());
	}catch (Exception e) {
		// TODO: handle exception
		e.printStackTrace();
		System.out.println("setValue方法出错");
	}
	}
	
	public static void getRs(User u ,ResultSet rs){
		try {
			u.setId(rs.getInt("id"));
			u.setName(rs.getString("name"));
			u.setUsername(rs.getString("username"));
			u.setPassword(rs.getString("password"));
			u.setRole(rs.getString("role"));
			u.setPicUrl(rs.getString("picUrl"));
			u.setPosition(rs.getString("position"));
			u.setPolitical(rs.getString("political"));
			u.setBirthday(rs.getString("birthday"));
			u.setEduLevel(rs.getString("eduLevel"));
			u.setEmail(rs.getString("email"));
			u.setPhone(rs.getString("phone"));
			u.setSex(rs.getString("sex"));
			u.setProfessionalSkill(rs.getString("professionalSkill"));
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			System.out.println("getRs出错");
		}
	}
	

}
